#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Created by ZhangYichao on 2018/5/8


# 下面这些变量需要您根据自己的具体情况选择
biaotou = ['FILE_NAME','CLASS_CODE', 'CLASS_NAME', 'ORIGINAL_NAME', 'SAS_NAME', 'ORIGINAL_LABEL','MAP_NAME',"PREFIX","METADATA_NAME","METADATA_IDENTIFY",
           'CLASS_CHILDNAME', 'SAS_DATA_TYPE', 'FIELDCODE_TABLECODE', 'FIELDCODE_VALUE','FIELDCODE_VALUE_CN_NAME','EDC_FIELDCODE_TABLECODE','EDC_FIELDCODE_VALUE','EDC_FIELDCODE_VALUE_CN_NAME']
comIndex = [0,1,2,3,4,5,6,7,8,9,
            12,13,14,15,16,17,18,19]
# 在哪里搜索多个表格
filelocation ="E:\\cs\\01work\\首都医科大学附属北京天坛医院\\天坛医院-病历补录\\天坛历史数据处理20180717\\检验表\\"
#E:\cs\01work\首都医科大学附属北京天坛医院\天坛医院-病历补录\天坛历史数据处理20180717\检验表
#filelocation ="E:\\cs\\01work\\首都医科大学附属北京天坛医院\\病历补录\\测试天坛变量是否正确20180713\\"
#u"E:\\cs\\01work\\2016-07天坛医院_脑血管病资源库\\病历补录\\测试天坛变量是否正确20180627\\"
# 当前文件夹下搜索的文件名后缀
fileform = "xlsx"
# 将合并后的表格存放到的位置
filedestination = "E:\\"
# 合并后的表格命名为file
file = "shujuyuan_6"

# 首先查找默认文件夹下有多少文档需要整合
import glob
import numpy as np
import traceback

filelocation=filelocation + "*." + fileform

print(filelocation)
filearray = []
for filename in glob.glob(filelocation.decode('utf8')):
    filearray.append(filename)
# 以上是从pythonscripts文件夹下读取所有excel表格，并将所有的名字存储到列表filearray
print("在默认文件夹下有%d个文档哦" % len(filearray))
ge = len(filearray)
matrix = [None] * ge
# 实现读写数据

# 下面是将所有文件读数据到三维列表cell[][][]中（不包含表头）
import xlrd

for i in range(ge):
    filePathName = filearray[i]
    fName=filePathName.split("\\")[len(filePathName.split("\\"))-1]
    print(fName)

    bk = xlrd.open_workbook(filePathName)
    try:
        sh = bk.sheet_by_name("Sheet1")
    except:
        print ("在文件%s中没有找到sheet1，读取文件数据失败,要不你换换表格的名字？" % filePathName)
    nrows = sh.nrows-9
    matrix[i] = [0] * (nrows - 1)
    ncols = len(comIndex)
    tmpTableCode=""

    for m in range(nrows - 1):
        matrix[i][m] = ["0"] * ncols

    for j in range(1, nrows):
        # print(sh.cell(j + 9, comIndex[4]).value)
        # print(sh.cell(j + 9, comIndex[4]).value == '')
        # print(sh.cell(j + 9, comIndex[6]).value)
        # print(sh.cell(j + 9, comIndex[6]).value == '')
        # print(sh.cell(j + 9, comIndex[12]).value)
        # print(sh.cell(j + 9, comIndex[12]).value == '')
        # print(sh.cell(j + 9, comIndex[13]).value)
        # print(sh.cell(j + 9, comIndex[13]).value == '')
        # print(sh.cell(j + 9, comIndex[14]).value)
        # print(sh.cell(j + 9, comIndex[14]).value == '')
        #
        # print(sh.cell(j + 9, comIndex[4]).value == '' and sh.cell(j + 9, comIndex[6]).value == '' \
        #       and sh.cell(j + 9, comIndex[12]).value == '' and sh.cell(j + 9, comIndex[13]).value == '' and sh.cell(
        #             j + 9, comIndex[14]).value == '')
        # print(
        # u"--------------------------------------------------------------------------------------------------------------------")

        if sh.cell(j + 9, comIndex[4]).value == '' and sh.cell(j + 9, comIndex[6]).value == '' \
              and sh.cell(j + 9, comIndex[12]).value == '' and sh.cell(j + 9, comIndex[13]).value == '' and sh.cell(
                    j + 9, comIndex[14]).value == '':
            continue

        for k in range(0, ncols):
            if k==0:
                matrix[i][j - 1][k]=fName
            else:
                try:
                    # 值域编码统一赋值 Begin
                    if comIndex[k]==14 and sh.cell(j + 9, comIndex[k]).value!=u"" and sh.cell(j + 9, comIndex[k+1]).value!=u"":
                        tmpTableCode=sh.cell(j + 9, comIndex[k]).value
                    if comIndex[k] == 14 and sh.cell(j + 9, comIndex[k]).value == u"" and (sh.cell(j + 9, comIndex[k+1]).value!=u""):
                        matrix[i][j - 1][k] = tmpTableCode
                    # 值域编码统一赋值 End
                    else:
                        matrix[i][j - 1][k] = sh.cell(j + 9, comIndex[k]).value
                except IOError as info:
                    print((j + 9,comIndex[k]))

# 下面是写数据到新的表格test.xls中
import xlwt

filename = xlwt.Workbook()
sheet = filename.add_sheet("Sheet1")
# 下面是把表头写上
for i in range(0, len(biaotou)):
    sheet.write(0, i, biaotou[i])
# 求和前面的文件一共写了多少行
zh = 1
for i in range(ge):
    for j in range(len(matrix[i])):
        if(matrix[i][j][0])=="0":
            continue

        for k in range(len(matrix[i][j])):
             sheet.write(zh, k, matrix[i][j][k])
        zh = zh + 1

filename.save(filedestination + file + ".xls")
print("我已经将%d个文件合并成1个文件，并命名为%s.xls.快打开看看正确不？" % (ge, filedestination + file ))
